package reports

import (
	"bytes"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"shqsoft.cn/pasnj/base"

	"github.com/jinzhu/gorm"

	"golang.org/x/text/language"
	"golang.org/x/text/message"
)

// DataRange 数据范围
type DataRange int

// iota 初始化后会自动递增
const (
	AllRange DataRange = iota
	Dept1Range
	Dept2Range
	UserRange
)

// Report 接口
type Report interface {
	SetParams(map[string]string) error
	SetDataRange(dRange DataRange, vals ...int)
	SetCanIdno(canIdno bool)
	GetConfig() *Config
	Create([]base.WhereCause, map[string]string) (int, error)
	GetDataList(int, int, string) ([]map[string]string, error)
	ExportXslx(string) (*bytes.Buffer, error)
}

// NewReport 新报表对象
func NewReport(name string) (rept Report, err error) {
	var config *Config
	if config, err = GetReportConfig(name); err != nil {
		return
	}
	for _, col := range config.Columns {
		if col.Format != "" {
			if col.DataType == "float" || col.DataType == "int" || col.DataType == "number" {
				if !strings.HasPrefix(col.Format, ".") || (!strings.HasSuffix(col.Format, "f") && !strings.HasSuffix(col.Format, "f%")) {
					return nil, fmt.Errorf("报表列%s的format定义的%s是无效！", col.Name, col.Format)
				}
			}
		}
	}
	if config.Type == "list" {
		rep := new(ListReport)
		rep.name = name
		rep.config = config
		rep.keyColID = -1
		rep.userColIDs = nil
		rept = rep
	} else {
		err = fmt.Errorf("报表类型%s还没有实现，敬请期待！", config.Type)
	}
	return
}

// ListReport 简单列表式报表
type ListReport struct {
	name           string
	paramVals      map[string]interface{}
	config         *Config
	tableName      string
	count          int
	whereStr       string
	whereParamVals []interface{}
	groupBy        []string
	orderBy        []string
	dataRange      DataRange
	rangeVals      []int
	keyColID       int
	userColIDs     []int
	canIndo        bool
}

// SetDataRange 设置数据查看范围
func (lr *ListReport) SetDataRange(dRange DataRange, vals ...int) {
	lr.dataRange = dRange
	lr.rangeVals = vals
}

// SetCanIdno 设置是否可查看身份证号
func (lr *ListReport) SetCanIdno(canIndo bool) {
	lr.canIndo = canIndo
}

// SetParams 设置报表参数
func (lr *ListReport) SetParams(paramVals map[string]string) (err error) {
	lr.paramVals = make(map[string]interface{}, len(paramVals))
	for _, p := range lr.config.Params {
		if _, ok := paramVals[p.Name]; !ok {
			return fmt.Errorf("参数名称%s在给定的map中没有找到！", p.Name)
		}
		if p.DataType == "date" {
			if lr.paramVals[p.Name], err = time.Parse("2006-01-02", paramVals[p.Name]); err != nil {
				return err
			}
		} else {
			lr.paramVals[p.Name] = paramVals[p.Name]
		}
	}
	return nil
}

// GetConfig 获取列定义
func (lr *ListReport) GetConfig() *Config {
	return lr.config
}

// Create 生成报表
func (lr *ListReport) Create(where []base.WhereCause, order map[string]string) (count int, err error) {
	if lr.tableName == "" {
		if lr.tableName, err = lr.parseTableName(lr.config.DataSource.Name); err != nil {
			return 0, err
		}
	}
	bc := lr.config.DataSource.BeforeCall
	if bc != nil && bc.Name != "" {
		if bc.Type == "proc" {
			if err = lr.execProc(bc.Name); err != nil {
				return
			}
		} else {
			err = fmt.Errorf("报表%s的回调类型%s还没有实现！", lr.config.Name, bc.Type)
		}
	}
	if lr.whereStr, lr.whereParamVals, err = base.ParseWhereCause(where); err != nil {
		return
	}
	rVals := lr.rangeVals
	if lr.dataRange == Dept1Range || lr.dataRange == Dept2Range {
		deptColName := "DPID"
		if lr.dataRange == Dept1Range {
			deptColName = "PDID"
		}
		sql := fmt.Sprintf("SELECT EMID FROM DIM_USER WHERE %s in (?)", deptColName)
		rows, err := base.DB.Raw(sql, rVals).Rows()
		if err != nil {
			return 0, err
		}
		defer rows.Close()
		emids := make([]int, 0, 20)
		for rows.Next() {
			var emid int
			rows.Scan(&emid)
			emids = append(emids, emid)
		}
		rVals = emids
	}
	uidCols := strings.Split(lr.config.UserIDColumn, ",")
	if lr.whereParamVals == nil {
		lr.whereParamVals = make([]interface{}, 0, 10)
	}
	if lr.dataRange != AllRange {
		if lr.whereStr != "" {
			lr.whereStr = lr.whereStr + " and "
		}
		lr.whereStr += " ("
		for i, uidCol := range uidCols {
			lr.whereStr += uidCol + " in (?)"
			if i < len(uidCols)-1 {
				lr.whereStr += " OR "
			} else {
				lr.whereStr += ") "
			}
			lr.whereParamVals = append(lr.whereParamVals, rVals)
		}
	}
	if lr.config.Where != "" {
		if lr.whereStr == "" {
			lr.whereStr += lr.config.Where
		} else {
			lr.whereStr += " and " + lr.config.Where
		}
	}
	count, err = lr.getCount()
	if err != nil {
		return
	}
	if order == nil || len(order) == 0 {
		if lr.config.OrderBy != "" {
			lr.orderBy = []string{lr.config.OrderBy}
		} else if lr.config.KeyColumn != "" {
			lr.orderBy = []string{lr.config.KeyColumn + " DESC"}
		} else {
			err = fmt.Errorf("报表%s的必须指定KeyColumn！", lr.config.Name)
		}
	} else {
		lr.orderBy = make([]string, 0, 3)
		for k, v := range order {
			if k == "" {
				continue
			}
			item := k + " " + v
			lr.orderBy = append(lr.orderBy, item)
		}
	}
	var rows *sql.Rows
	rows, err = base.DB.Table(lr.tableName).Rows()
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	cols, err := base.GetColumns(rows)
	if err != nil {
		return 0, err
	}
	ulen := len(uidCols)
	lr.userColIDs = make([]int, ulen, ulen)
	for i, col := range cols {
		if lr.config.KeyColumn == col {
			lr.keyColID = i
			continue
		}
		for j, uidCol := range uidCols {
			if uidCol == col {
				lr.userColIDs[j] = i
				break
			}
		}
		for j, col2 := range lr.config.Columns {
			if strings.ToUpper(col2.Name) == strings.ToUpper(col) {
				lr.config.Columns[j].DataIndex = strconv.Itoa(i)
				break
			}
		}
	}
	if lr.config.KeyColumn != "" && lr.keyColID == -1 {
		err = fmt.Errorf("报表%s指定的主键%s在数据源中不存在！", lr.config.Name, lr.config.KeyColumn)
	}
	if lr.config.UserIDColumn != "" && len(lr.userColIDs) == 0 {
		err = fmt.Errorf("报表%s指定的员工ID键%s在数据源中不存在！", lr.config.Name, lr.config.UserIDColumn)
	}
	lr.count = count
	return
}
func (lr *ListReport) getCount() (count int, err error) {
	tName := lr.tableName
	err = base.DB.Table(tName).Where(lr.whereStr, lr.whereParamVals...).Count(&count).Error
	return
}

// GetDataList 获取数据
func (lr *ListReport) GetDataList(offset, limit int, mgxx string) (datas []map[string]string, err error) {
	datas = make([]map[string]string, 0, 20)
	var db *gorm.DB
	db = base.DB.Table(lr.tableName)
	if lr.whereStr != "" {
		db = db.Where(lr.whereStr, lr.whereParamVals...)
	}
	if lr.orderBy != nil {
		order := strings.Join(lr.orderBy, " ")
		db = db.Order(order)
	}
	if offset != -1 {
		db = db.Offset(offset)
	}
	if limit > 0 {
		db = db.Limit(limit)
	}
	var rows *sql.Rows
	if rows, err = db.Rows(); err != nil {
		return nil, err
	}
	defer rows.Close()
	cols, err := base.GetColumns(rows)
	if err != nil {
		return nil, err
	}
	clen := len(cols)
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			return nil, err
		}
		mapVal, err := lr.toMap(values, true, mgxx)
		if err != nil {
			return nil, err
		}
		datas = append(datas, mapVal)
	}
	rows.Close()
	return datas, nil
}

// ExportXslx 导出Xslx格式
func (lr *ListReport) ExportXslx(mgxx string) (buff *bytes.Buffer, err error) {
	xslx, err := NewXlsx()
	if err != nil {
		return nil, err
	}
	xslx.AddColumns(lr.config.Columns)

	db := base.DB.Table(lr.tableName)
	if lr.whereStr != "" {
		db = db.Where(lr.whereStr, lr.whereParamVals...)
	}
	if lr.orderBy != nil {
		order := strings.Join(lr.orderBy, " ")
		db = db.Order(order)
	}
	var rows *sql.Rows
	if rows, err = db.Rows(); err != nil {
		return nil, err
	}
	defer rows.Close()
	cols, err := base.GetColumns(rows)
	if err != nil {
		return nil, err
	}
	clen := len(cols)
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			return nil, err
		}
		mapVal, err := lr.toMap(values, false, mgxx)
		if err != nil {
			return nil, err
		}
		if err = xslx.AddRow(mapVal); err != nil {
			return nil, err
		}
	}
	buff = xslx.Export()
	return
}
func (lr *ListReport) parseTableName(name string) (string, error) {
	if lr.paramVals == nil {
		return "", fmt.Errorf("解析报表%s的表名出错，参数没有初始化！", lr.config.Name)
	}
	var pName string
	for k, v := range lr.paramVals {
		pName = "@" + k
		var val string
		if dVal, ok := v.(time.Time); ok {
			val = dVal.Format("20060102")
		} else {
			val = base.ToString(v)
		}
		name = strings.Replace(name, pName, val, -1)
	}
	if strings.Index(name, "@") != -1 {
		return "", fmt.Errorf("解析报表%s的表名出错，解析之后是%s！", lr.config.Name, name)
	}
	if lr.config.DataSource.Type == "sql" {
		name = fmt.Sprintf("(%s) t ", name)
	}
	return name, nil
}
func (lr *ListReport) execProc(name string) error {
	proc := make([]rune, 0, len(name))
	pNames := make([]string, 0, 5)
	var pName []rune
	find := false
	for _, v := range name {
		if !find {
			if v == '@' {
				find = true
				pName = make([]rune, 0, 10)
			} else {
				proc = append(proc, v)
			}
		} else {
			if v == ',' {
				find = false
				pNames = append(pNames, string(pName))
			} else if v == '@' {
				return fmt.Errorf("表达是%s定义错误，多个参数之间要以逗号隔开！", name)
			} else {
				pName = append(pName, v)
			}
		}
	}
	if find {
		pNames = append(pNames, string(pName))
	}
	procName := string(proc)
	pVals := make([]interface{}, len(pNames), len(pNames))
	for i, n := range pNames {
		if v, ok := lr.paramVals[n]; ok {
			pVals[i] = v
		} else {
			return fmt.Errorf("参数%s没有赋值或者不存在！", n)
		}
	}
	err := lr.execSQLProc(procName, pVals...)
	return err
}
func (lr *ListReport) toMap(row []sql.NullString, isFormat bool, mgxx string) (map[string]string, error) {
	dataMap := make(map[string]string)
	var val string
	print := message.NewPrinter(language.English)
	if lr.keyColID != -1 {
		dataMap[lr.config.KeyColumn] = row[lr.keyColID].String
	}
	if lr.userColIDs != nil {
		uidCols := strings.Split(lr.config.UserIDColumn, ",")
		for i, uidCol := range uidCols {
			dataMap[uidCol] = row[i].String
		}
	}
	for _, col := range lr.config.Columns {
		if col.DataIndex == "" {
			err := fmt.Errorf("字段%s配置重复或者在数据源中没有找到", col.Name)
			return nil, err
		}
		val = ""
		index, _ := strconv.Atoi(col.DataIndex)
		if row[index].Valid {
			val = row[index].String
		}
		toVal := val
		if val != "" {
			format := col.Format
			if col.DataType == "float" && isFormat {
				fVal, err := strconv.ParseFloat(val, 64)
				if err != nil {
					return nil, err
				}
				if format == "" {
					format = ".2f"
				}
				isP := strings.HasSuffix(format, "f%")
				if isP {
					fVal = fVal * 100
					format = string(([]byte(format))[:len(format)-1])
				}
				toVal = print.Sprintf("%"+format, fVal)
				if isP {
					toVal = toVal + "%"
				}
			} else if col.DataType == "date" {
				dVal, err := time.Parse(time.RFC3339, val)
				if err != nil {
					return nil, err
				}
				if format == "" {
					format = "2006-01-02"
				}
				toVal = dVal.Format(col.Format)
			} else if col.DataType == "bool" {
				bVal, err := strconv.ParseBool(val)
				if err != nil || !bVal {
					toVal = "否"
				} else {
					toVal = "是"
				}
			} else if col.Name == "证件号码" || col.Name == "证件编号" || col.Name == "IDNO" {
				valBytes := []byte(val)
				if !strings.Contains(mgxx, "M1") {
					if len(valBytes) < 6 {
						toVal = string(valBytes)
					} else {
						toVal = string(valBytes[:6]) + "********" + string(valBytes[len(valBytes)-4:])
					}
				}
			} else if col.Name == "卡号" {
				valBytes := []byte(val)
				if !strings.Contains(mgxx, "M3") {
					if len(valBytes) < 6 {
						toVal = string(valBytes)
					} else {
						toVal = string(valBytes[:6]) + "******" + string(valBytes[len(valBytes)-4:])
					}
				}
			} else if col.Name == "手机" || col.Name == "手机号码" || col.Name == "联系方式" || col.Name == "手机号" || col.Name == "联系电话" || col.Name == "电话" {
				valBytes := []byte(val)
				if !strings.Contains(mgxx, "M2") {
					if len(valBytes) < 3 {
						toVal = string(valBytes)
					} else {
						toVal = string(valBytes[:3]) + "****" + string(valBytes[len(valBytes)-4:])
					}
				}
			} else if col.Name == "家庭住址" || col.Name == "家庭地址" {
				valBytes := []byte(val)
				if !strings.Contains(mgxx, "M4") {
					if len(valBytes) < 5 {
						toVal = string(valBytes)
					} else {
						toVal = string(valBytes[:5]) + "**********"
					}
				}
			} else if col.Name == "出生日期" || col.Name == "生日" {
				valBytes := []byte(val)
				if !strings.Contains(mgxx, "M5") {
					if len(valBytes) < 4 {
						toVal = string(valBytes)
					} else {
						toVal = string(valBytes[:4]) + "****"
					}
				}
			}
			// else if (col.Name == "证件号码" || col.Name == "证件编号" || col.Name == "IDNO") && len(val) == 18 && !lr.canIndo {
			// 	valBytes := []byte(val)
			// 	//toVal = string(valBytes[:6]) + "******" + string(valBytes[14:])
			// 	toVal = string(valBytes[:16]) + "**"
			// }

		}
		dataMap[col.DataIndex] = toVal
	}
	return dataMap, nil
}

// ExecProc 执行存储过程
func (lr *ListReport) execSQLProc(procName string, params ...interface{}) error {
	var buff bytes.Buffer
	buff.WriteString("exec ")
	buff.WriteString(procName)
	buff.WriteString(" ")
	pLen := len(params)
	for i := 0; i < pLen; i++ {
		buff.WriteString("?,")
	}
	if pLen > 0 {
		buff.Truncate(buff.Len() - 1)
	}
	sql := buff.String()
	return base.DB.Exec(sql, params...).Error
}
